﻿using System.Data.OracleClient;
using System.Data;
using System.Collections;
using System.Collections.Generic;

namespace DataServices.Code
{
    public class Connect_DataBase
    {
        #region khai bao bien
        string _connectstring = "Data Source=FLEXTEST_109;Persist Security Info=True;User ID=HOSTTEST;Password=HOST;";
        OracleDataAdapter _da = new OracleDataAdapter();
        OracleCommand _cmd = new OracleCommand();
        OracleConnection _con = new OracleConnection();
        DataTable _table = new DataTable();

        #endregion

        #region ham connect
        private void OpenConnect()
        {
            _con.ConnectionString = _connectstring;
            _con.Open();
        }

        private void CloseConnect()
        {
            _con.Close();
        }

        private string RunSql_ByParameter(string sql, List<string> _paras, List<string> _values)
        {
            OpenConnect();
            _cmd = new OracleCommand(sql);
            for (int i = 0; i < _paras.Count; i++)
            {
                _cmd.Parameters.Add(_paras[i], OracleType.NVarChar, 255).Value = _values[i];
            }
            _cmd.Connection = _con;
            try
            {
                _cmd.ExecuteNonQuery();
                CloseConnect();
            }
            catch (System.Exception ex)
            {
                CloseConnect();
                return ex.Message;
            }
            return "OK";
        }

        private string Delete_BySqlID(string sql)
        {
            OpenConnect();
            _cmd = new OracleCommand(sql);
            _cmd.Connection = _con;
            try
            {
                _cmd.ExecuteNonQuery();
                CloseConnect();
            }
            catch (System.Exception ex)
            {
                CloseConnect();
                return ex.Message;
            }
            return "OK";
        }

        #endregion

        #region ham check thong tin
        // check login
        public bool CheckLogin(string _username, string _password)
        {
            _cmd = _con.CreateCommand();
            _cmd.CommandType = CommandType.Text;
            _cmd.CommandText = "SELECT * FROM tbl_user WHERE username like :username and pass like :password AND Active='Y'";
            _cmd.Parameters.Add("username", OracleType.VarChar).Value = _username;
            _cmd.Parameters.Add("password", OracleType.VarChar).Value = _password;
            _cmd.Connection = _con;
            OpenConnect();
            _da = new OracleDataAdapter(_cmd);
            _da.Fill(_table);
            CloseConnect();
            if (_table.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }

        public DataTable Return_Datatable(string _sql)
        {
            OpenConnect();
            _da = new OracleDataAdapter(_sql, _con);
            DataTable result = new DataTable();
            _da.Fill(result);
            CloseConnect();
            return result;
        }

        public bool Check_TrungGiaTri(string _value, string _tableName, string _columnName)
        {
            _cmd = new OracleCommand("select * from " + _tableName + " where " + _columnName + " like :param1", _con);
            _cmd.Parameters.Add("param1", OracleType.NVarChar, 255).Value = _value;
            OpenConnect();
            DataTable result = new DataTable();
            _da.SelectCommand = _cmd;
            _da.Fill(result);
            if (result.Rows.Count > 0)
            {
                CloseConnect();
                return true;
            }
            CloseConnect();
            return false;
        }

        public DataTable SearchView_BySQL(string _sql)
        {
            OpenConnect();
            _da = new OracleDataAdapter(_sql, _con);
            DataTable result = new DataTable();
            _da.Fill(result);
            CloseConnect();
            return result;
        }
        #endregion

        #region add, edit, delete

        public string AddNewRow(string _fldname, DataTable _tableValue)
        {
            // lấy toàn bộ cột trong bảng cần insert
            List<string> _para = new List<string>();
            List<string> _values = new List<string>();
            DataTable _tableColumn = new DataTable();
            _tableColumn = this.Return_Datatable("select * from fldmaster where objname like '" + _fldname + "' and visible='Y' order by odrnum");

            // tạo câu lệnh insert dựa theo bảng vừa lấy
            string sql = "Insert into " + _fldname.Split('.')[1] + " (";
            foreach (DataRow row in _tableColumn.Rows)
            {
                sql += row["fldname"].ToString() + ",";
                _para.Add(row["fldname"].ToString());
            }
            sql = sql.Substring(0, sql.Length - 1) + ") values(";
            foreach (DataRow row in _tableColumn.Rows)
            {
                sql += ":" + row["fldname"].ToString() + ",";
            }
            sql = sql.Substring(0, sql.Length - 1) + ")";
            // so sánh theo tên cột để lấy giá trị cần insert, truyền vào parameter
            foreach (DataRow row in _tableColumn.Rows)
            {
                foreach (DataRow value in _tableValue.Rows)
                {
                    if (value[0].ToString() == row["fldname"].ToString())
                    {
                        _values.Add(value[1].ToString());
                    }
                }
            }

            // chạy lệnh insert, thành công trả lại True, lỗi trả lại messeage
            try
            {
                RunSql_ByParameter(sql, _para, _values);
            }
            catch (System.Exception ex)
            {
                return ex.Message;
            }

            return "OK";
        }

        public string EditRow(string _fldname, DataTable _tableValue, string _ID_ColumnName, string _ID)
        {
            // lấy toàn bộ cột trong bảng cần insert
            List<string> _para = new List<string>();
            List<string> _values = new List<string>();
            DataTable _tableColumn = new DataTable();
            _tableColumn = this.Return_Datatable("select * from fldmaster where objname like '" + _fldname + "' and visible='Y' order by odrnum");

            // tạo câu lệnh UPDATE dựa theo bảng vừa lấy
            string sql = "Update " + _fldname.Split('.')[1] + " SET ";
            foreach (DataRow row in _tableColumn.Rows)
            {
                // loại trừ trường ID, ko cần UPDATE
                if (row["fldname"].ToString() != _ID_ColumnName)
                {
                    sql += row["fldname"].ToString() + "=:" + row["fldname"].ToString() + ",";
                    _para.Add(row["fldname"].ToString());
                }
            }
            sql = sql.Substring(0, sql.Length - 1) + " where " + _ID_ColumnName + "='" + _ID + "'";

            // so sánh theo tên cột để lấy giá trị , truyền vào parameter
            foreach (DataRow row in _tableColumn.Rows)
            {
                foreach (DataRow value in _tableValue.Rows)
                {
                    if (value[0].ToString() == row["fldname"].ToString() && row["fldname"].ToString() != _ID_ColumnName)
                    {
                        _values.Add(value[1].ToString());
                    }
                }
            }

            // chạy lệnh, thành công trả lại True, lỗi trả lại messeage
            try
            {
                RunSql_ByParameter(sql, _para, _values);
            }
            catch (System.Exception ex)
            {
                return ex.Message;
            }

            return "OK";
        }

        public string DeleteRow(string _fldname, string _ID, string _ID_ColumnName)
        {
            string sql = "delete from " + _fldname + " where " + _ID_ColumnName + "='" + _ID + "'";
            // chạy lệnh, thành công trả lại True, lỗi trả lại messeage
            try
            {
                Delete_BySqlID(sql);
            }
            catch (System.Exception ex)
            {
                return ex.Message;
            }

            return "OK";
        }

        #endregion


       
    }
}
